﻿/*
   PROCEDURES LIST :
   	GET_DOMAINS (p_Thesaurus TEXT) RETURNS SETOF TEXT
   	GET_CONCEPTS (p_Domain TEXT) RETURNS SETOF TEXT
   	
	Obtenir les informations concernant un concept	
	GET_DOMAIN (p_Concept TEXT) RETURNS TEXT
	GET_TERMVDT (p_Concept TEXT) RETURNS TEXT
	GET_TERMNVDTS (p_Concept TEXT) RETURNS SETOF TEXT
	GET_TS (p_Concept TEXT) RETURNS SETOF TEXT
	GET_TG (p_Concept TEXT) RETURNS SETOF TEXT
	GET_ASSOCIATIONS (p_Concept TEXT) RETURNS SETOF RECORD
*/

/*
*	Function : GET_DOMAINS
*	Description : Retourne une liste de nom de domaines d'un thesaurus
*/


CREATE OR REPLACE FUNCTION GET_DOMAINS (p_Thesaurus TEXT) RETURNS SETOF TEXT AS $$
DECLARE
	thesaurus_oid OID;
	domains_oids OID[];
BEGIN
/* 0- verification des entrées */
	thesaurus_oid = (SELECT OID FROM Thesaurus WHERE Nom = p_Thesaurus);
	
	IF (thesaurus_oid IS NOT NULL) THEN 
		domains_oids = domains_oids || (SELECT Domains FROM Thesaurus WHERE OID = thesaurus_oid);

		RETURN QUERY 
			SELECT Nom FROM Domains WHERE ARRAY[OID] <@ domains_oids ORDER BY Nom;
	END IF;
END;
$$ LANGUAGE plpgsql;

/*
*	Function : GET_CONCEPTS
*	Description : Retourne une liste de nom de concepts d'un domain
*/
CREATE OR REPLACE FUNCTION GET_CONCEPTS (p_Domain TEXT) RETURNS SETOF TEXT AS $$
DECLARE
	domain_oid OID;
	concepts_oids OID[];
BEGIN
/* 0- verification des entrées */
	domain_oid = (SELECT OID FROM Domains WHERE Nom = p_Domain);
	IF (domain_oid IS NOT NULL) THEN 
		concepts_oids = concepts_oids || (SELECT Concepts FROM Domains WHERE OID = domain_oid);
		
		RETURN QUERY
			SELECT Nom FROM Concepts WHERE ARRAY[OID] <@ concepts_oids ORDER BY Nom;
	END IF;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : GET_DOMAIN
*	Description : Retourne le nom de domain d'un concept
*/
CREATE OR REPLACE FUNCTION GET_DOMAIN (p_Concept TEXT) RETURNS TEXT AS $$
DECLARE
	concept_oid OID;
	domain_oid OID;
	domain_nom TEXT;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NULL) THEN 
		RETURN NULL;
	END IF;
	
	domain_oid = (SELECT Domain FROM Concepts WHERE OID = concept_oid);
	domain_nom = (SELECT Nom FROM Domains WHERE OID = domain_oid);
	
	RETURN domain_nom;
END;
$$ LANGUAGE plpgsql;

/*
*	Function : GET_TermVedette
*	Description : Retourne le nom du term vedette d'un concept
*/
CREATE OR REPLACE FUNCTION GET_TERMVDT (p_Concept TEXT) RETURNS TEXT AS $$
DECLARE
	concept_oid OID;
	termVdt_oid OID;
	termVdt_nom TEXT;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NULL) THEN 
		RETURN NULL;
	END IF;
	
	termVdt_oid = (SELECT TermVedette FROM Concepts WHERE OID = concept_oid);
	termVdt_nom = (SELECT Nom FROM TermVdts WHERE OID = termVdt_oid);
	
	RETURN termVdt_nom;
END;
$$ LANGUAGE plpgsql;

/*
*	Function : GET_TermNonVedetts
*	Description : Retourne une liste de nom des term non vedette d'un concept
*/
CREATE OR REPLACE FUNCTION GET_TERMNVDTS (p_Concept TEXT) RETURNS SETOF TEXT AS $$
DECLARE
	concept_oid OID;
	termVdt_oid OID;
	termNonVdts_oids OID[];
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NOT NULL) THEN 
		termVdt_oid = (SELECT TermVedette FROM Concepts WHERE OID = concept_oid);
		termNonVdts_oids = termNonVdts_oids || (SELECT EP FROM TermVdts WHERE OID = termVdt_oid);
		
		RETURN QUERY
			SELECT Nom FROM TermNonVdts WHERE ARRAY[OID] <@ termNonVdts_oids ORDER BY Nom;
	END IF;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : GET_TS
*	Description : Retourne une liste de nom des concepts spécifiques d'un concept
*/
CREATE OR REPLACE FUNCTION GET_TS (p_Concept TEXT) RETURNS SETOF TEXT AS $$
DECLARE
	concept_oid OID;
	ts_oids OID[];
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NOT NULL) THEN 
		ts_oids = ts_oids || (SELECT TS FROM Concepts WHERE OID = concept_oid);
		
		RETURN QUERY
			SELECT Nom FROM Concepts WHERE ARRAY[OID] <@ ts_oids ORDER BY Nom;
	END IF;
END;
$$ LANGUAGE plpgsql;

/*
*	Function : GET_TG
*	Description : Retourne une liste de nom des concepts géneriques d'un concept
*/
CREATE OR REPLACE FUNCTION GET_TG(p_Concept TEXT) RETURNS SETOF text AS $$
DECLARE
	concept_oid OID;
	tg_oids OID[];
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NOT NULL) THEN 
		tg_oids = tg_oids || (SELECT TG FROM Concepts WHERE OID = concept_oid);
		
		RETURN QUERY
			SELECT Nom FROM Concepts WHERE ARRAY[OID] <@ tg_oids;
	END IF;
END;
$$ LANGUAGE plpgsql;


/*
*	Function : GET_ASSOCIATIONS
*	Description : Retourne une liste des assocaitions reliées à un concept
*	Appel :  L'appel de cette procedure est particulier car elle retourne une collection de ligne de table avec un type dynamique,
*
*       Requête :  SELECT * FROM GET_ASSOCIATIONS('Nom du concept') AS (concept_oid OID,association TEXT,concept TEXT, Reflex BOOLEAN);
*       Les champs de la table qui est retournée sont : concept_oid, association, concept							
*/
CREATE OR REPLACE FUNCTION GET_ASSOCIATIONS (p_Concept TEXT) RETURNS SETOF RECORD AS $$
DECLARE
	concept_oid OID;
	rec RECORD;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NOT NULL) THEN 

	 FOR rec IN (SELECT Concepts.OID, Associations.Nom,Concepts.Nom, Associations.Reflex FROM Associations,Concepts 
			WHERE (Concept1 = concept_oid OR Concept2 = concept_oid) AND Concepts.OID <> concept_oid 
			AND   (Concepts.OID = Concept1 OR Concepts.OID = Concept2) 
			GROUP BY Concepts.OID,Concepts.Nom,Associations.Nom,Associations.Reflex
			ORDER BY Concepts.nom)
	 LOOP
	   RETURN NEXT rec ;
	 END LOOP ;
	 
	 RETURN ;

	END IF;
END;
$$ LANGUAGE plpgsql;

/*
*	Function : GET_ASSOCIATIONS1
*	Description : Retourne une liste des assocaitions reliées à un concept (qui se trouve dans concept1)
*	(Pour l'admin)
*/
CREATE OR REPLACE FUNCTION GET_ASSOCIATIONS1 (p_Concept TEXT) RETURNS SETOF RECORD AS $$
DECLARE
	concept_oid OID;
	rec RECORD;
BEGIN
/* 0- verification des entrées */
	concept_oid = (SELECT OID FROM Concepts WHERE Nom = p_Concept);
	IF (concept_oid IS NOT NULL) THEN 

	 FOR rec IN (SELECT Concepts.OID, Associations.Nom,Concepts.Nom, Associations.Reflex FROM Associations,Concepts 
			WHERE (Concept1 = concept_oid) AND Concepts.OID <> concept_oid 
			AND   (Concepts.OID = Concept1 OR Concepts.OID = Concept2) 
			GROUP BY Concepts.OID,Concepts.Nom,Associations.Nom,Associations.Reflex
			ORDER BY Concepts.nom)
	 LOOP
	   RETURN NEXT rec ;
	 END LOOP ;
	 
	 RETURN ;

	END IF;
END;
$$ LANGUAGE plpgsql;

/*
*	Function : EXIST_ASSOCIATION
*	Description : Retourne l'oid d'une association si elle existe, 0 sinon
*/
CREATE OR REPLACE FUNCTION EXIST_ASSOCIATION (p_Nom TEXT, p_Concept1 TEXT, p_Concept2 TEXT, p_Reflex BOOLEAN) RETURNS OID AS $$
DECLARE
	tmp OID;
BEGIN

	SELECT OID FROM Associations WHERE Nom = p_Nom AND
				Concept1 = (SELECT OID FROM Concepts WHERE Nom = p_Concept1) AND
				Concept2 = (SELECT OID FROM Concepts WHERE Nom = p_Concept2) AND
				Reflex = p_Reflex INTO tmp;
	IF tmp IS NULL THEN
		tmp = 0;
	END IF;
				
	RETURN tmp;
						
END;
$$ LANGUAGE plpgsql;
